package com.mairuide.bill.titlepool;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.apache.pdfbox.pdmodel.PDDocument;
import org.apache.pdfbox.text.PDFTextStripper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.mairuide._frame.base.BaseRepository;
import com.mairuide._frame.base.BaseServiceProxy;
import com.mairuide._frame.utils.SpringUtil;
import com.mairuide.bill.DealFlow;
import com.mairuide.bill.DealFlowService;
import com.mairuide.bill.utils.AnalysisPDFUtils;
import com.mairuide.bill.utils.DateUtils;
import com.mairuide.bill.utils.ExcelUtils;
import com.mairuide.platmanager.PlatManager;

/**
 * @author: zyh
 * @description:
 * @date: 2018/11/15 0015
 * @company: MRD
 */
@Service
public class DealFlowTitleService extends BaseServiceProxy<DealFlowTitle> {

    @Resource
    private DealFlowTitleRepository dealFlowTitleRepository;
    @Autowired
    private DealFlowService dealFlowService;
    @Autowired
    private DealFlowTitlePoolRepository dealFlowTitlePoolRepository;
    @Resource
	private JdbcTemplate jdbcTemplate;
    @Override
    protected BaseRepository<DealFlowTitle> getBaseRepository() {
        return dealFlowTitleRepository;
    }

    /**
     * 解析建行新模板，此模板有4个sheet页，只解析2，3页
     * @param wb
     * @return
     */
    public List<DealFlow> ccbNewTemplate(Workbook wb){
        Sheet sheet = null;
        Row row = null;
        List<DealFlow> list = new ArrayList<>();
        if (wb!=null){
            // 获取第2个sheet,个人活期明细
//            String sheetName = wb.getSheetName(1);
            sheet = wb.getSheetAt(1);
            // 获取最大行数
            int rownum = sheet.getLastRowNum();
            int i;
            lop1:for (i = 0; i < rownum; i++) {
                row = sheet.getRow(i);
                String s = ExcelUtils.getCellValue(row.getCell(0)).trim();
                if (s.startsWith("客户名称")){
                    //主体map放个人信息
                    Map<String,String> titlemap = new HashMap<>();
                    String[] split = s.split("，");
                    for (String ss:split){
                        String[] strings = ss.split(":");
                        if (strings.length==2){
                            titlemap.put(strings[0].trim(),strings[1].trim());
                        } else {
                            titlemap.put(strings[0].trim(),"");
                        }
                    }
                    i=i+1;
                    row = sheet.getRow(i);
                    int cells = row.getPhysicalNumberOfCells();
                    String [] colnums = new String[40];
                    for (int k =0;k<cells;k++){
                        colnums[k] = ExcelUtils.getCellValue(row.getCell(k)).trim();
                    }
                    //交易信息
                    for (i=i+1;i<=rownum;i++){
                        row = sheet.getRow(i);
                        if (ExcelUtils.isMergedRegion(sheet,i,0)){
                            i=i-1;
                            continue lop1;
                        }
                        Map<String,String> map = new HashMap<>();
                        for (int k = 0; k<cells; k++) {
                            map.put(colnums[k],ExcelUtils.getCellValue(row.getCell(k)).trim());
                        }
                        map.putAll(titlemap);
                        DealFlow deal = new DealFlow();
                        deal.setAccountBody(map.get("客户名称"));
                        deal.setAccountBodyNumber(map.get("客户账号"));
                        deal.setAccountBodyCard(map.get("卡号"));
                        deal.setCurrency(map.get("币别"));
                        String date = map.get("交易日期")+" "+map.get("交易时间");
                        Date date1 = DateUtils.getDate(date);
                        deal.setTradeDate(new Timestamp(date1.getTime()));
                        deal.setContentAbstract(map.get("摘要"));
                        if (new BigDecimal(map.get("贷方发生额")).compareTo(new BigDecimal(0))>0){
                            deal.setSign(0);
                            deal.setAccountMoney(new BigDecimal(map.get("贷方发生额")));
                        } else if (new BigDecimal(map.get("借方发生额")).compareTo(new BigDecimal(0))>0){
                            deal.setSign(1);
                            deal.setAccountMoney(new BigDecimal(map.get("借方发生额")));
                        }
                        deal.setSurplusMoney(new BigDecimal(map.get("账户余额")));
                        deal.setTradingOrganizationNumber(map.get("交易机构号"));
                        deal.setAccountAddress(map.get("交易机构名称"));
                        deal.setAccountRivalNumber(map.get("对方账号"));
                        deal.setAccountRival(map.get("对方户名"));
                        deal.setAccountRivalBank(map.get("对方行名"));
                        deal.setTellerNumber(map.get("柜员号"));
                        deal.setSerialNumber(map.get("交易流水号"));
                        deal.setRemarks(map.get("交易备注"));

                        if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                            deal.setAccountBodyCard(deal.getAccountBodyNumber());
                        }
                        if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                            deal.setAccountRivalCard(deal.getAccountRivalNumber());
                        }

                        list.add(deal);
                    }
                }else {
                    continue;
                }
            }
            // 获取第3个sheet,个人活期明细
            sheet = wb.getSheetAt(2);
            // 获取最大行数
            int rownum2 = sheet.getLastRowNum();
            int t;
            lop2:for (t = 0; t < rownum2; t++) {
                row = sheet.getRow(t);
                String s = ExcelUtils.getCellValue(row.getCell(0)).trim();
                if (s.startsWith("客户名称")){
                    //主体map放个人信息
                    Map<String,String> titlemap = new HashMap<>();
                    String[] split = s.split("，");
                    for (String ss:split){
                        String[] strings = ss.split(":");
                        if (strings.length==2){
                            titlemap.put(strings[0].trim(),strings[1].trim());
                        } else {
                            titlemap.put(strings[0].trim(),"");
                        }
                    }
                    t=t+1;
                    row = sheet.getRow(t);
                    int colls = row.getPhysicalNumberOfCells();
                    String [] colnums = new String[40];
                    for (int k =0;k<colls;k++){
                        colnums[k] = ExcelUtils.getCellValue(row.getCell(k)).trim();
                    }
                    //交易信息
                    for (t=t+1;t<=rownum2;t++){
                        row = sheet.getRow(t);
                        if (ExcelUtils.isMergedRegion(sheet,t,0)){
                            t=t-1;
                            continue lop2;
                        }
                        Map<String,String> map = new HashMap<>();
                        for (int k = 0; k<colls; k++) {
                            map.put(colnums[k],ExcelUtils.getCellValue(row.getCell(k)).trim());
                        }
                        map.putAll(titlemap);
                        DealFlow deal = new DealFlow();
                        deal.setAccountBody(map.get("客户名称"));
                        deal.setAccountBodyNumber(map.get("客户账号"));
                        deal.setAccountBodyCard(map.get("一本通账号或卡号"));
                        deal.setCurrency(map.get("币别"));
                        String date = map.get("交易日期")+" "+map.get("交易时间");
                        Date date1 = DateUtils.getDate(date);
                        deal.setTradeDate(new Timestamp(date1.getTime()));
                        deal.setContentAbstract(map.get("摘要"));
                        if ("贷".equals(map.get("借贷方标志"))){
                            deal.setSign(0);
                        } else {
                            deal.setSign(1);
                        }
                        deal.setAccountMoney(new BigDecimal(map.get("交易金额")));
                        deal.setSurplusMoney(new BigDecimal(map.get("账户余额")));
                        deal.setTradingOrganizationNumber(map.get("交易机构号"));
                        deal.setAccountAddress(map.get("交易机构名称"));
                        deal.setAccountRivalNumber(map.get("对方账号"));
                        deal.setAccountRival(map.get("对方户名"));
                        deal.setTellerNumber(map.get("柜员号"));
                        deal.setAccountMode(map.get("交易渠道"));
                        deal.setSerialNumber(map.get("交易流水号"));

                        if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                            deal.setAccountBodyCard(deal.getAccountBodyNumber());
                        }
                        if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                            deal.setAccountRivalCard(deal.getAccountRivalNumber());
                        }

                        list.add(deal);
                    }
                }else {
                    continue;
                }
            }
        }

        return list;
    }

    /**
     * 西谷直属模板，此模板有4个sheet页，只解析2，3页
     * @param wb
     * @return
     */
    public List<DealFlow> xiguTemplate(Workbook wb){
        Sheet sheet = null;
        Row row = null;
        List<DealFlow> list = new ArrayList<>();
        if (wb!=null){
            // 获取第2个sheet,企业活期
//            String sheetName = wb.getSheetName(1);
            sheet = wb.getSheetAt(1);
            // 获取最大行数
            int rownum = sheet.getLastRowNum();
            int i;
            lop1:for (i = 0; i < rownum; i++) {
                row = sheet.getRow(i);
                String s = ExcelUtils.getCellValue(row.getCell(0)).trim();
                if (s.startsWith("客户名称")){
                    //主体map放个人信息
                    Map<String,String> titlemap = new HashMap<>();
                    String[] split = s.split("，");
                    for (String ss:split){
                        String[] strings = ss.split(":");
                        if (strings.length==2){
                            titlemap.put(strings[0].trim(),strings[1].trim());
                        } else {
                            titlemap.put(strings[0].trim(),"");
                        }
                    }
                    i=i+1;
                    row = sheet.getRow(i);
                    int cells = row.getPhysicalNumberOfCells();
                    String [] colnums = new String[40];
                    for (int k =0;k<cells;k++){
                        colnums[k] = ExcelUtils.getCellValue(row.getCell(k)).trim();
                    }
                    //交易信息
                    for (i=i+1;i<=rownum;i++){
                        row = sheet.getRow(i);
                        if (ExcelUtils.isMergedRegion(sheet,i,0)){
                            i=i-1;
                            continue lop1;
                        }
                        Map<String,String> map = new HashMap<>();
                        for (int k = 0; k<cells; k++) {
                            map.put(colnums[k],ExcelUtils.getCellValue(row.getCell(k)).trim());
                        }
                        map.putAll(titlemap);
                        DealFlow deal = new DealFlow();
                        deal.setAccountBody(map.get("客户名称"));
                        deal.setAccountBodyNumber(map.get("客户账号"));
//                        deal.setAccountBodyCard(map.get("卡号"));
                        deal.setCurrency(map.get("币别"));
                        String date = map.get("交易日期")+" "+map.get("交易时间");
                        Date date1 = DateUtils.getDate(date);
                        if (date1!=null)deal.setTradeDate(new Timestamp(date1.getTime()));
                        deal.setContentAbstract(map.get("摘要描述"));
                        if (new BigDecimal(map.get("贷方发生额")).compareTo(BigDecimal.ZERO)>0){
                            deal.setSign(0);
                            deal.setAccountMoney(new BigDecimal(map.get("贷方发生额")));
                        } else if (!new BigDecimal(map.get("借方发生额")).equals(BigDecimal.ZERO)){
                            deal.setSign(1);
                            deal.setAccountMoney(new BigDecimal(map.get("借方发生额")));
                        }
                        deal.setSurplusMoney(new BigDecimal(map.get("账户余额")));
                        deal.setTradingOrganizationNumber(map.get("交易机构号"));
                        deal.setAccountAddress(map.get("交易机构名称"));
                        deal.setAccountRivalNumber(map.get("对方账号"));
                        deal.setAccountRival(map.get("对方户名"));
                        deal.setAccountRivalBank(map.get("对方行名"));
                        deal.setTellerNumber(map.get("柜员号"));
                        deal.setSerialNumber(map.get("交易流水号"));
                        deal.setRemarks(map.get("扩充备注"));

                        if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                            deal.setAccountBodyCard(deal.getAccountBodyNumber());
                        }
                        if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                            deal.setAccountRivalCard(deal.getAccountRivalNumber());
                        }

                        list.add(deal);
                    }
                }else {
                    continue;
                }
            }
            // 获取第3个sheet,企业定期明细信息
            sheet = wb.getSheetAt(2);
            // 获取最大行数
            int rownum2 = sheet.getLastRowNum();
            int t;
            lop2:for (t = 0; t < rownum2; t++) {
                row = sheet.getRow(t);
                String s = ExcelUtils.getCellValue(row.getCell(0)).trim();
                if (s.startsWith("客户名称")){
                    //主体map放个人信息
                    Map<String,String> titlemap = new HashMap<>();
                    String[] split = s.split("，");
                    for (String ss:split){
                        String[] strings = ss.split(":");
                        if (strings.length==2){
                            titlemap.put(strings[0].trim(),strings[1].trim());
                        } else {
                            titlemap.put(strings[0].trim(),"");
                        }
                    }
                    t=t+1;
                    row = sheet.getRow(t);
                    int colls = row.getPhysicalNumberOfCells();
                    String [] colnums = new String[40];
                    colnums[0] = "交易日期";
                    for (int k =1;k<colls;k++){
                        colnums[k] = ExcelUtils.getCellValue(row.getCell(k)).trim();
                    }
                    //交易信息
                    for (t=t+1;t<=rownum2;t++){
                        row = sheet.getRow(t);
                        if (ExcelUtils.isMergedRegion(sheet,t,0)){
                            t=t-1;
                            continue lop2;
                        }
                        Map<String,String> map = new HashMap<>();
                        for (int k = 0; k<colls; k++) {
                            map.put(colnums[k],ExcelUtils.getCellValue(row.getCell(k)).trim());
                        }
                        map.putAll(titlemap);
                        DealFlow deal = new DealFlow();
                        deal.setAccountBody(map.get("客户名称"));
                        deal.setAccountBodyNumber(map.get("客户账号"));
//                        deal.setAccountBodyCard(map.get("一本通账号或卡号"));
                        deal.setCurrency(map.get("币别"));
                        String date = map.get("交易日期")+" "+map.get("交易时间");
                        Date date1 = DateUtils.getDate(date);
                        if (date1!=null)deal.setTradeDate(new Timestamp(date1.getTime()));
                        deal.setContentAbstract(map.get("摘要描述"));
                        if ("贷方".equals(map.get("借贷方标志"))){
                            deal.setSign(0);
                        } else if ("借方".equals(map.get("借贷方标志"))){
                            deal.setSign(1);
                        }
                        deal.setAccountMoney(new BigDecimal(map.get("交易金额")));
                        deal.setSurplusMoney(new BigDecimal(map.get("账户余额")));
                        deal.setTradingOrganizationNumber(map.get("交易机构号"));
                        deal.setAccountAddress(map.get("交易机构名称"));
                        deal.setAccountRivalNumber(map.get("对方账号"));
                        deal.setAccountRival(map.get("对方户名"));
                        deal.setTellerNumber(map.get("柜员号"));
                        deal.setAccountMode(map.get("交易渠道"));
                        deal.setSerialNumber(map.get("交易流水号"));

                        if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                            deal.setAccountBodyCard(deal.getAccountBodyNumber());
                        }
                        if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                            deal.setAccountRivalCard(deal.getAccountRivalNumber());
                        }

                        list.add(deal);
                    }
                }else {
                    continue;
                }
            }
        }

        return list;
    }

    /**
     * 广发模板，此模板有4个sheet页，只解析2，3页
     * @param wb
     * @return
     */
    public List<DealFlow> guangFaTemplate(Workbook wb){
        Sheet sheet = null;
        Row row = null;
        List<DealFlow> list = new ArrayList<>();

        if (wb!=null){
            // 获取第2个sheet,企业活期
//            String sheetName = wb.getSheetName(1);
            sheet = wb.getSheetAt(1);
            // 获取最大行数
            int rownum = sheet.getLastRowNum();
            row = sheet.getRow(0);
            int cells1 = row.getPhysicalNumberOfCells();
            String cols1[]=new String[56];
            for (int i=0;i<cells1;i++){
                cols1[i] = ExcelUtils.getCellValue(row.getCell(i)).trim();
            }
            for (int i = 1; i < rownum; i++) {
                //交易信息
                row = sheet.getRow(i);
                if (!ExcelUtils.isBlankLine(row))continue;
                Map<String,String> map = new HashMap<>();
                for (int k = 0; k<cells1; k++) {
                    map.put(cols1[k],ExcelUtils.getCellValue(row.getCell(k)).trim());
                }

                DealFlow deal = new DealFlow();
                deal.setAccountBody(map.get("客户名称"));
                deal.setAccountBodyNumber(map.get("账号"));
                deal.setAccountBodyCard(map.get("卡号"));
                deal.setCurrency(map.get("交易币种"));
                String date = map.get("交易日期")+" "+map.get("交易时间");
                Date date1 = DateUtils.getDate(date);
                if (date1!=null)deal.setTradeDate(new Timestamp(date1.getTime()));
                deal.setContentAbstract(map.get("摘要中文"));
                deal.setAccountMoney(new BigDecimal(map.get("交易金额")));
                if ("入账".equals(map.get("借贷\n" +
                        "标识"))){
                    deal.setSign(0);
                } else if ("出账".equals(map.get("借贷\n" +
                        "标识"))){
                    deal.setSign(1);
                }
                deal.setSurplusMoney(new BigDecimal(map.get("余额")));
                deal.setTradingOrganizationNumber(map.get("交易行"));
                deal.setAccountRivalNumber(map.get("对手账号"));
                deal.setAccountRival(map.get("对手名"));
                deal.setAccountRivalBank(map.get("对方行名"));
                deal.setRemarks(map.get("备注"));

                if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                    deal.setAccountBodyCard(deal.getAccountBodyNumber());
                }
                if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                    deal.setAccountRivalCard(deal.getAccountRivalNumber());
                }

                list.add(deal);

            }
            // 获取第3个sheet,个人活期明细
            sheet = wb.getSheetAt(2);
            // 获取最大行数
            int rownum2 = sheet.getLastRowNum();
            row = sheet.getRow(0);
            int cells2 = row.getPhysicalNumberOfCells();
            String cols2[]=new String[56];
            for (int i=0;i<cells2;i++){
                cols2[i] = ExcelUtils.getCellValue(row.getCell(i)).trim();
            }
            for (int i = 1; i < rownum2; i++) {
                //交易信息
                row = sheet.getRow(i);
                if (!ExcelUtils.isBlankLine(row))continue;
                Map<String,String> map = new HashMap<>();
                for (int k = 0; k<cells2; k++) {
                    map.put(cols2[k],ExcelUtils.getCellValue(row.getCell(k)).trim());
                }

                DealFlow deal = new DealFlow();
                deal.setAccountBody(map.get("客户名称"));
                deal.setAccountBodyNumber(map.get("本方账号"));
                deal.setAccountBodyCard(map.get("本方交易介质"));
                deal.setCurrency(map.get("交易\n" +
                        "货币"));
                String date = map.get("交易日期")+" "+map.get("交易时间");
                Date date1 = DateUtils.getDate(date);
                if (date1!=null)deal.setTradeDate(new Timestamp(date1.getTime()));
                deal.setContentAbstract(map.get("摘要中文"));
                deal.setAccountMoney(new BigDecimal(map.get("交易金额")));
                if ("入账".equals(map.get("借贷\n" +
                        "标识"))){
                    deal.setSign(0);
                } else if ("出账".equals(map.get("借贷\n" +
                        "标识"))){
                    deal.setSign(1);
                }
                deal.setSurplusMoney(new BigDecimal(map.get("当前金额")));
                deal.setTradingOrganizationNumber(map.get("交易行"));
                deal.setAccountRivalNumber(map.get("对方账号"));
                deal.setAccountRival(map.get("对手账号名称"));
                deal.setAccountRivalBank(map.get("对手账号行所号"));
                deal.setRemarks(map.get("备注"));

                if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                    deal.setAccountBodyCard(deal.getAccountBodyNumber());
                }
                if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                    deal.setAccountRivalCard(deal.getAccountRivalNumber());
                }

                list.add(deal);
            }
        }

        return list;
    }

    /**
     * PDF模板
     * @param files
     * @return
     */
    public Map<String ,Object> pdfAnalysis(MultipartFile files, String caseId, PlatManager platManager, String uuid){
        String content="";
        Map<String ,Object> resultMap = new HashMap<>();
        List<DealFlow> list = new ArrayList<>();
        int errorNum = 0;
        try{
            PDDocument document = PDDocument.load(files.getInputStream());
            PDFTextStripper stripper=new PDFTextStripper();
            content = stripper.getText(document);

            String[] split = content.split("\r\n");
            if (split[1].contains("[动态查询]")){
                list = AnalysisPDFUtils.pdfTemplate1(split, caseId, platManager.getId(), uuid);
            }else if (split[0].contains("客户名: ")){
                list = AnalysisPDFUtils.pdfTemplate2(split, caseId, platManager.getId(), uuid);
            }
            List<DealFlow> temp = new ArrayList<DealFlow>();
            for(int i=0;i<list.size();i++) {
            	if(StringUtils.isEmpty(list.get(i).getAccountBodyCard()) && StringUtils.isEmpty(list.get(i).getAccountBodyNumber())) {
            		temp.add(list.get(i));
            	}
                if(i==list.size()-1 || i%500==499) {
                    dealFlowService.batchSave(temp);
                    temp = new ArrayList<DealFlow>();
                }
            }
            resultMap.put("successNum",list.size());
            resultMap.put("errorNum",errorNum);
//            resultMap.put("list",list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (list.size()<1){
                resultMap.put("successNum",0);
                resultMap.put("errorNum",errorNum);
//                resultMap.put("list",list);
            }
            return resultMap;
        }
    }
    /**
     * PDF模板
     * @param files
     * @return
     */
    public Map<String ,Object> pdfAnalysisMapping(MultipartFile files, String caseId, PlatManager platManager, String uuid){
        String content="";
        Map<String ,Object> resultMap = new HashMap<>();
        List<DealFlow> list = new ArrayList<>();
        try{
            PDDocument document = PDDocument.load(files.getInputStream());
            PDFTextStripper stripper=new PDFTextStripper();
            content = stripper.getText(document);

            String[] split = content.split("\r\n");
            if (split[1].contains("[动态查询]")){
                list = AnalysisPDFUtils.pdfTemplate1(split, caseId, platManager.getId(), uuid);
            }else if (split[0].contains("客户名: ")){
                list = AnalysisPDFUtils.pdfTemplate2(split, caseId, platManager.getId(), uuid);
            }
            resultMap.put("data",list);
        } catch (Exception e) {
            e.printStackTrace();
        } 
        return resultMap;
    }
    /**
     * 邮政模板
     * @return
     */
    public List<DealFlow> youZhengTemplate(Workbook wb){
        Sheet sheet = null;
        Row row = null;
        List<Map<String,String>> list = new ArrayList<>();
        List<DealFlow> result = new ArrayList<>();
        if (wb!=null){
            sheet = wb.getSheetAt(0);
            // 获取最大行数
            int rownum = sheet.getLastRowNum();
            Map<String ,String> titleMap = null;
            //交易信息
            for (int i=0;i<=rownum;i++){
                row = sheet.getRow(i);
                if (ExcelUtils.isBlankLine(row)) {
                    short cells = row.getLastCellNum();
                    String cellValue = ExcelUtils.getCellValue(row.getCell(cells-1));
                    if (cellValue.contains("交易金额")) { //找到交易信息
                        boolean line = ExcelUtils.isBlankLine(sheet.getRow(i - 2));
                        if (line&&!"".equals(ExcelUtils.getCellValue(sheet.getRow(i-2).getCell(cells-1)).trim())){//这个表格是否和上一个信息链接     //没有信息链接
                            titleMap = new HashMap<>();
                            String title1 = ExcelUtils.getCellValue(sheet.getRow(i-5).getCell(cells-1));
                            title1 = title1.replace(String.valueOf(new char[]{160})," ").trim();
                            String[] split = title1.split("\\s+");
                            if (title1.contains(":")) {
                                String s1 = split[0].split(":")[1];// 开户机构号
                                String s2 = split[1].split(":")[1];//开户机构名称 account_body_bank
                                titleMap.put("开户机构名称", s2);
                            } else {
                                String s1 = split[0].split("：")[1];// 开户机构号
                                String s2 = split[1].split("：")[1];//开户机构名称 account_body_bank
                                titleMap.put("开户机构名称", s2);
                            }

                            String title2 = ExcelUtils.getCellValue(sheet.getRow(i-4).getCell(cells-1));
                            String[] split1 = title2.split("\\s");
                            String regEx = "(\\d+)";
                            Pattern pat = Pattern.compile(regEx);
                            for (String s:split1){
                                Matcher mat = pat.matcher(s);
                                if(mat.find()){
                                    String s3 = mat.group(1); // 账号/ 卡号  account_body_card
                                    titleMap.put("账号",s3);
                                }
                            }
                            String name = title2.contains("：")?split[split.length - 1].split("：")[1]:split[split.length - 1].split(":")[1];//户名 account_body
                            titleMap.put("户名",name);

                            String title3 = ExcelUtils.getCellValue(sheet.getRow(i-3).getCell(cells-1));


                            String title4 = ExcelUtils.getCellValue(sheet.getRow(i-2).getCell(cells-1));
                            String[] split4 = title4.contains("：")?title4.split("："):title4.split(":");
                            for (int j=0;j<split4.length;j++){
                                switch (j) {
                                    case 0 : break;
                                    case 1 : titleMap.put("币种",split4[j].split("\\s+")[0]);break; //  币种 currency
                                    case 2 : String dd=split4[j].split("\\s+")[0];break; //资 金 形  态
                                    case 3 : String dss=split4[j].split("\\s+")[0];break;//账户状态
                                    default: break;
                                }

                            }

                            Map <String ,String> bodyMap = null;
                            for (i=i+2 ;i<rownum;i++){
                                String context = ExcelUtils.getCellValue(sheet.getRow(i).getCell(cells-1));
                                if (context.contains("┴"))break;
                                String[] split2 = context.split("│");
                                if (!"".equals(split2[1].trim())){
                                    bodyMap = new HashMap<>();
                                    bodyMap.put("交易日期",split2[2].trim());
                                    bodyMap.put("交易渠道",split2[3].trim());
                                    bodyMap.put("摘要",split2[4].trim());
                                    bodyMap.put("交易金额",split2[5].trim());
                                    bodyMap.put("账户余额",split2[6].trim());
                                    bodyMap.put("对方账号/卡号/汇票号",split2[7].trim());
                                    if (split2.length>9){
                                        bodyMap.put("原子账号",split2[8].trim());
                                        bodyMap.put("交易机构名称",split2[9].trim());
                                    }else {
                                        bodyMap.put("交易机构名称",split2[8].trim());
                                    }
                                } else {
                                    bodyMap.put("交易日期",(bodyMap.get("交易日期")+split2[2]).trim());
                                    bodyMap.put("交易渠道",(bodyMap.get("交易渠道")+split2[3]).trim());
                                    bodyMap.put("摘要",(bodyMap.get("摘要")+split2[4]).trim());
                                    bodyMap.put("交易金额",(bodyMap.get("交易金额")+split2[5]).trim());
                                    bodyMap.put("账户余额",(bodyMap.get("账户余额")+split2[6]).trim());
                                    bodyMap.put("对方账号/卡号/汇票号",(bodyMap.get("对方账号/卡号/汇票号")+split2[7]).trim());
                                    if (split2.length>9){
                                        bodyMap.put("原子账号",(bodyMap.get("原子账号")+split2[8]).trim());
                                        bodyMap.put("交易机构名称",(bodyMap.get("交易机构名称")+split2[9]).trim());
                                    }else {
                                        bodyMap.put("交易机构名称",(bodyMap.get("交易机构名称")+split2[8]).trim());
                                    }
                                }
                                bodyMap.putAll(titleMap);
                                list.add(bodyMap);
                            }
                        } else {//和上一个表链接
                            Map <String ,String> bodyMap = null;
                            for (i=i+2 ;i<rownum;i++){
                                String context = ExcelUtils.getCellValue(sheet.getRow(i).getCell(cells-1));
                                if (context.contains("┴"))break;
                                String[] split2 = context.split("│");
                                if (!"".equals(split2[1].trim())){
                                    bodyMap = new HashMap<>();
                                    bodyMap.put("交易日期",split2[2].trim());
                                    bodyMap.put("交易渠道",split2[3].trim());
                                    bodyMap.put("摘要",split2[4].trim());
                                    bodyMap.put("交易金额",split2[5].trim());
                                    bodyMap.put("账户余额",split2[6].trim());
                                    bodyMap.put("对方账号/卡号/汇票号",split2[7].trim());
                                    if (split2.length>9){
                                        bodyMap.put("原子账号",split2[8].trim());
                                        bodyMap.put("交易机构名称",split2[9].trim());
                                    }else {
                                        bodyMap.put("交易机构名称",split2[8].trim());
                                    }
                                } else {
                                    bodyMap.put("交易日期",(bodyMap.get("交易日期")+split2[2]).trim());
                                    bodyMap.put("交易渠道",(bodyMap.get("交易渠道")+split2[3]).trim());
                                    bodyMap.put("摘要",(bodyMap.get("摘要")+split2[4]).trim());
                                    bodyMap.put("交易金额",(bodyMap.get("交易金额")+split2[5]).trim());
                                    bodyMap.put("账户余额",(bodyMap.get("账户余额")+split2[6]).trim());
                                    bodyMap.put("对方账号/卡号/汇票号",(bodyMap.get("对方账号/卡号/汇票号")+split2[7]).trim());
                                    if (split2.length>9){
                                        bodyMap.put("原子账号",(bodyMap.get("原子账号")+split2[8]).trim());
                                        bodyMap.put("交易机构名称",(bodyMap.get("交易机构名称")+split2[9]).trim());
                                    }else {
                                        bodyMap.put("交易机构名称",(bodyMap.get("交易机构名称")+split2[8]).trim());
                                    }
                                }
                                bodyMap.putAll(titleMap);
                                list.add(bodyMap);
                            }
                        }
                    }
                }
            }
        }
        Set<Map<String,String>> set = new HashSet<>(list);
        list = new ArrayList<>(set);
        for (Map<String,String > mm:list){
            DealFlow deal = new DealFlow();
            deal.setAccountBodyBank(mm.get("开户机构名称"));
            deal.setAccountBodyCard(mm.get("账号"));
            deal.setAccountBodyNumber(mm.get("账号"));
            deal.setAccountBody(mm.get("户名"));
            deal.setCurrency(mm.get("币种"));
            Date date = DateUtils.getDate(mm.get("交易日期"));
            if (date!=null)deal.setTradeDate(new Timestamp(date.getTime()));
            deal.setAccountMode(mm.get("交易渠道"));
            deal.setContentAbstract(mm.get("摘要"));
            deal.setAccountMoney(new BigDecimal(mm.get("交易金额").replaceAll(",","")));
            deal.setSurplusMoney(new BigDecimal(mm.get("账户余额").replaceAll(",","")));
            deal.setAccountRivalNumber(mm.get("对方账号/卡号/汇票号"));//对方账号
            deal.setAccountRivalCard(mm.get("对方账号/卡号/汇票号"));//对方卡号
            deal.setAccountAddress(mm.get("交易机构名称"));
            result.add(deal);
        }
        return result;
    }
    /**
     *  匹配标题头
     * @param paramTempMap
     * @return
     */
    public Map<String,Object> TempTitleMapping(Map<String, String> paramTempMap) {
    	Map<String,Object> map = new HashMap<String,Object>();
    	Map<String,String> mappingTitle = new HashMap<String,String>();
    	List<Integer> notTile = new ArrayList<Integer>();
		if(null != paramTempMap) {
			paramTempMap.remove("TEMP_0");
			for(String s1 : paramTempMap.keySet()){
				//遍历map的键
				String mv = paramTempMap.get(s1).toString();
				List<DealFlowTitlePool> dfp = dealFlowTitlePoolRepository.findByBankTitleNameAndClassNameOrderById(mv, "com.mairuide.bill.DealFlow");
				if(null != dfp && dfp.size() > 0) {
					notTile.add(dfp.get(0).getTitleId());
					DealFlowTitle dft = dealFlowTitleRepository.findOne(dfp.get(0).getTitleId());
					mappingTitle.put(s1, dft.getMappingField());
				}else {
					mappingTitle.put(s1, "未知");
				}
			}

			//查询所有未匹配上的标题
		    List<DealFlowTitle> notInTitle = dealFlowTitleRepository.findByIdNotIn(notTile);
		    if(notInTitle != null && notInTitle.size() > 0) {
		    	List<String> notTitles = notInTitle.stream().map(e -> e.getMappingField()).collect(Collectors.toList());
		    	map.put("notInTitle", notTitles);
		    }else {
		    	map.put("notInTitle", null);
		    }
  		    map.put("mappingTitle", mappingTitle);
			return map;
		}
		return map;
	}
    
    /**
     * 通过标题名称查询对应字段
     * @param mv
     * @return
     */
    public String getMappingFieldByMv(String mf){
		DealFlowTitle dft = dealFlowTitleRepository.findByMappingField(mf);
		if(null != dft) {
			return dft.getHeadTitle();
		}
		return null;
    }
    /**
     * 通过选择标题行获取匹配结果
     * @param selectRowIndex
     * @param caseId
     * @return
     */
    public List<Map<String,Object>> getMappingResultBySelectRowIndex(String selectRowIndex,String caseId) {
    	String sql = "select * from TEMP_"+caseId+" t where t.TEMP_0 > "+selectRowIndex;
    	return jdbcTemplate.queryForList(sql);
    }
    
    /**
     * 插入操作
     * @param insertSql
     */
    public Integer excuteDealFlowInsert(List<String> insertSql) {
		if(null != insertSql && insertSql.size() > 0) {
			int size = 0;
			for(String sql : insertSql) {
				jdbcTemplate.execute(sql);
				size ++;
			}
			return size;
		}
		return 0;
	}
}
